﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonVAPCF;
using System.Data;
using System.Data.SqlClient;
namespace ITD_VAPCF.NganQuy.BLL
{
    public static class NgayQuyBLL
    {

        public static DataTable Get_LoaiTien(string ngay, string macn)
        {
            try
            {
                using (SqlConnection sqlConnect = new SqlConnection(CommonVAPCF.GlobalVariables.g_strConnectionMsSql))
                {
                    SqlCommand sqlCommand = new SqlCommand("sp_Get_LoaiTien", sqlConnect);
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add("@Ngay", SqlDbType.VarChar).Value = ngay;
                    sqlCommand.Parameters.Add("@macn", SqlDbType.VarChar).Value = macn;
                    SqlDataAdapter daDataAdapter = new SqlDataAdapter();
                    daDataAdapter.SelectCommand = sqlCommand;
                    sqlConnect.Open();
                    DataTable dt = new DataTable();
                    daDataAdapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                return null;
            }


        }
        public static DataTable Get_LoaiGiaoDich()
        {
            try
            {
                using (SqlConnection sqlConnect = new SqlConnection(CommonVAPCF.GlobalVariables.g_strConnectionMsSql))
                {
                    string sql = "select  ma, ten, ten_tat, ghi_chu,ma_nhom" +
                         " from Dm_Loai_Giao_Dich where ghi_chu1 = '01' or ghi_chu1 ='02'";

                    SqlCommand sqlCommand = new SqlCommand(sql, sqlConnect);
                    sqlCommand.CommandType = CommandType.Text;
                    SqlDataAdapter daDataAdapter = new SqlDataAdapter();
                    daDataAdapter.SelectCommand = sqlCommand;
                    sqlConnect.Open();
                    DataTable dt = new DataTable();
                    daDataAdapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                return null;
            }


        }
        public static DataTable DanhSachGiaoDich(string TuNgay, string DenNgay)
        {
            DataTable dt = new DataTable();
            SqlConnection myConnect = new SqlConnection(Common.GlobalVar.g_strConnectionMsSql);
            SqlCommand myCommand = new SqlCommand("sp_NQ_GiaoDichThuChi", myConnect);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add("@TuNgay", SqlDbType.VarChar).Value = TuNgay;
            myCommand.Parameters.Add("@DenNgay", SqlDbType.VarChar).Value = DenNgay;
            SqlDataAdapter ad = new SqlDataAdapter(myCommand);
            myConnect.Open();
            ad.Fill(dt);
            myConnect.Close();
            return dt;
        }
        public static DataTable DanhSachGiaoDich_DoiTien(string TuNgay, string DenNgay)
        {
            string str = "";
            str = "SELECT distinct 0 Chon,CONVERT(varchar(10),ngd.Ngay,103) NgayGD,nttg.*,loai_giao_dich loai_giao_dich_id ";
            str += ",ngd.SoTien,ngd.SoChungTu SoCT, UsersNew, UsersModify   ";
            str += " FROM NQ_GiaoDich ngd INNER JOIN NQ_ThongTinGD nttg ON ngd.GiaoDichID = nttg.GiaoDich_ID WHERE ngd.Loai_Giao_Dich = '00' AND CONVERT(varchar(10),ngd.Ngay,112) <= '" + DenNgay + "' and CONVERT(varchar(10),ngd.Ngay,112) >= '" + TuNgay + "'";
            DataTable dt = new DataTable();
            SqlConnection myConnect = new SqlConnection(Common.GlobalVar.g_strConnectionMsSql);
            SqlCommand myCommand = new SqlCommand(str, myConnect);
            myCommand.CommandType = CommandType.Text;
            SqlDataAdapter ad = new SqlDataAdapter(myCommand);
            myConnect.Open();
            ad.Fill(dt);
            myConnect.Close();
            return dt;
        }
    }
}
